import Database from 'better-sqlite3'
import fs from 'fs'
import path from 'path'
import { sqliteFilePath, dataDir } from './common-lib/paths.js'

let db = null

function ensureDir (dir) {
  if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true })
}

export function initSQLite () {
  ensureDir(dataDir)
  const file = sqliteFilePath()
  db = new Database(file)
  db.pragma('journal_mode = WAL')
  db.pragma('foreign_keys = ON')
  createTables()
  return db
}

export function getDB () {
  if (!db) return initSQLite()
  return db
}

function createTables () {
  const d = getDB()
  d.exec(`
    CREATE TABLE IF NOT EXISTS users (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      money INTEGER DEFAULT 0,
      love INTEGER DEFAULT 0,
      level INTEGER DEFAULT 0,
      PRIMARY KEY (user_id, group_id)
    );

    CREATE TABLE IF NOT EXISTS battle (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      level INTEGER DEFAULT 0,
      attack INTEGER DEFAULT 0,
      defense INTEGER DEFAULT 0,
      speed INTEGER DEFAULT 0,
      PRIMARY KEY (user_id, group_id)
    );

    CREATE TABLE IF NOT EXISTS inventory (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      item_id TEXT NOT NULL,
      item_json TEXT NOT NULL,
      ts INTEGER DEFAULT (strftime('%s','now')),
      PRIMARY KEY (user_id, group_id, item_id)
    );

    CREATE TABLE IF NOT EXISTS signin (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      streak INTEGER DEFAULT 0,
      last_signed TEXT,
      PRIMARY KEY (user_id, group_id)
    );

    CREATE TABLE IF NOT EXISTS boosts (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      type TEXT NOT NULL,
      value INTEGER DEFAULT 0,
      PRIMARY KEY (user_id, group_id, type)
    );

    CREATE TABLE IF NOT EXISTS workshop (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      level INTEGER DEFAULT 1,
      slots INTEGER DEFAULT 1,
      data_json TEXT,
      PRIMARY KEY (user_id, group_id)
    );

    CREATE TABLE IF NOT EXISTS synthesis_history (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      ts INTEGER NOT NULL,
      record_json TEXT NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_synth_history ON synthesis_history(user_id, group_id, ts);

    CREATE TABLE IF NOT EXISTS relationship (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      data_json TEXT,
      PRIMARY KEY (user_id, group_id)
    );

    CREATE TABLE IF NOT EXISTS quest (
      user_id TEXT NOT NULL,
      group_id TEXT NOT NULL,
      data_json TEXT,
      PRIMARY KEY (user_id, group_id)
    );
  `)
}

export default { initSQLite, getDB }